package com.FH.util;

import java.util.ArrayList;

public class ImportExcelTable extends AccessDB  {

    public int ImportExcel(ArrayList<ArrayList<String>> dataList, String tableName){
//        PoiExcelHelper helper = null;
//        if(filePath.contains(".xls")){
//            helper = new PoiExcel2k3Helper();
//        }
//        if(filePath.contains(".xlsx")){
//            helper = new PoiExcel2k7Helper();
//        }
//        PoiExcelHelper helper = new PoiExcel2k3Helper();
        // 读取excel文件数据
//        ArrayList<ArrayList<String>> dataList = helper.readExcel(filePath,0,"1-");

        String insertSqlColum = "";

        //创建数据表
        String sql ="create table " + tableName + " (";

        ArrayList<String> data = dataList.get(0);

        for(int i=0;i<data.size();i++){

            if(i < data.size() -1){
                sql += "`" + data.get(i).toString() + "`" + " VARCHAR(100), ";
                insertSqlColum += "`" + data.get(i).toString() + "`," ;
            }

            if(i == data.size() - 1){
                sql += "`" + data.get(i).toString() + "`" + " VARCHAR(100)";
                insertSqlColum += "`" + data.get(i).toString() + "`" ;
            }
        }

        sql += ")";

        int Creatresult = this.createReportingTo(sql);  //创建数据表
//        int Creatresult = 1;

        if(Creatresult > 0){
            for(int i =1;i<dataList.size();i++){
                ArrayList<String> datalist = dataList.get(i);
                //excel插入语句
                String insertSql = " insert into " + tableName + "(" + insertSqlColum + ") values (";
                for(int j=0;j<data.size();j++){

                    if(j < data.size() - 1 ){
                        insertSql += "'" + datalist.get(j).toString() + "',";
                    }

                    if(j == data.size() -1){
                        insertSql += "'" + datalist.get(j).toString() + "'";
                    }

                }

                insertSql += ")";

                int result = this.addReportingTo(insertSql);
            }
        }

        return Creatresult;

    }

}
